package com.example.qrcodecamera;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;
import com.example.qrcodecamera.MaterialInfo;

public class MaterialDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "MaterialDB.db";
    private static final int DATABASE_VERSION = 3; // 版本号+1以触发升级
    private static final String TABLE_NAME = "materials";

    public static final String TABLE_MYSQL = "mysql_records";

    // 字段定义
    private static final String COL_TIME = "storage_time";  // 入库时间
    private static final String COL_ID = "material_id";     // 物料编号
    private static final String COL_GROSS = "gross_weight"; // 总重量
    private static final String COL_NET = "net_weight";     // 当前重量

    public MaterialDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建新表结构（包含5个字段，含主键id）
        db.execSQL("CREATE TABLE " + TABLE_NAME + " (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                COL_TIME + " TEXT," +    // 入库时间
                COL_ID + " TEXT," +       // 物料编号
                COL_GROSS + " REAL," +    // 总重量
                COL_NET + " REAL)");      // 当前重量

        db.execSQL("CREATE TABLE " + TABLE_MYSQL + " (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                COL_TIME + " TEXT," +    // 入库时间
                COL_ID + " TEXT," +       // 物料编号
                COL_GROSS + " REAL," +    // 总重量
                COL_NET + " REAL)");      // 当前重量
    }



    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 删除旧表并创建新表
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MYSQL);
        onCreate(db);
    }

    // 插入数据（新增入库时间字段）
    public boolean insertData(String storageTime, String materialId,
                              String grossWeight, String netWeight) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COL_TIME, storageTime);
        values.put(COL_ID, materialId);
        values.put(COL_GROSS, grossWeight);
        values.put(COL_NET, netWeight);
        return db.insert(TABLE_NAME, null, values) != -1;
    }

    // 分页查询（按新字段顺序）
    public List<MaterialInfo> getPagedData(int page, int pageSize) {
        List<MaterialInfo> data = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(
                "SELECT " + COL_TIME + "," + COL_ID + "," + COL_GROSS + "," + COL_NET +
                        " FROM " + TABLE_NAME +
                        " LIMIT " + pageSize +
                        " OFFSET " + (page-1)*pageSize, null);

        if (cursor.moveToFirst()) {
            do {
                data.add(new MaterialInfo(
                        cursor.getString(0),  // 入库时间
                        cursor.getString(1),  // 物料编号
                        cursor.getString(2),  // 总重量
                        cursor.getString(3)   // 当前重量
                ));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return data;
    }

    // 获取总记录数
    public int getTotalCount() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_NAME, null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        cursor.close();
        return count;
    }

    public boolean insertMYSQLData(String storageTime, String materialId,
                              String grossWeight, String netWeight) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COL_TIME, storageTime); // 使用常量
        values.put(COL_ID, materialId);
        values.put(COL_GROSS, grossWeight);
        values.put(COL_NET, netWeight);
        return db.insert(TABLE_MYSQL, null, values) != -1;
    }

    public int getmysqlTotalCount() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_NAME, null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        cursor.close();
        return count;
    }

    public List<MaterialInfo> getmysqlMaterialById(String materialId) {
        List<MaterialInfo> data = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(
                TABLE_MYSQL,
                new String[]{COL_TIME, COL_ID, COL_GROSS, COL_NET}, // 使用常量
                COL_ID + " = ?", // 使用常量
                new String[]{materialId},
                null, null, null
        );

        if (cursor.moveToFirst()) {
            do {
                MaterialInfo info = new MaterialInfo(// id
                        cursor.getString(0),       // material_id
                        cursor.getString(1),
                        cursor.getString(2),
                        cursor.getString(3)  // material_name
                );
                data.add(info);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return data;
    }
}